﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;

namespace WCFService
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    public class Service1 : IService1
    {
        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }

        public CompositeType GetDataUsingDataContract(CompositeType composite)
        {
            if (composite == null)
            {
                throw new ArgumentNullException("composite");
            }
            if (composite.BoolValue)
            {
                composite.StringValue += "Suffix";
            }
            return composite;
        }
        public bool insert_KH_NGHIEP_VU(string ma_nv, string ten_nv,DateTime ngay_bd)
        {
            bool Ins = false;
            DAL.ConnectDB.connect();
            var cmd = DAL.ConnectDB.connection.CreateCommand();
            cmd.CommandText = "insert_kh_nghiep_vu.insert_kh_nghiep_vu_by";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ma_nghiep_vu", OracleDbType.Varchar2).Value = ma_nv;
            cmd.Parameters.Add("ten_nghiep_vu", OracleDbType.Varchar2).Value = ten_nv;
            cmd.Parameters.Add("ngay_bat_dau", OracleDbType.Date).Value = ngay_bd;
            int insert = cmd.ExecuteNonQuery();

            if (insert != 0)
            {
                Ins = true;
            }
            return Ins;
        }
        public bool edit_KH_NGHIEP_VU(Int64 id_nv, string ma_nv, string ten_nv,DateTime ngay_bd)
        {
            bool Upd = false;
            DAL.ConnectDB.connect();
            var cmd = DAL.ConnectDB.connection.CreateCommand();
            cmd.CommandText = "edit_kh_nghiep_vu.edit_kh_nghiep_vu_by";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("id_nghiep_vu", OracleDbType.Int64).Value = id_nv;
            cmd.Parameters.Add("ma_nghiep_vu", OracleDbType.Varchar2).Value = ma_nv;
            cmd.Parameters.Add("ten_nghiep_vu", OracleDbType.Varchar2).Value = ten_nv;
            cmd.Parameters.Add("ngay_bat_dau", OracleDbType.Date).Value = ngay_bd;
            int insert = cmd.ExecuteNonQuery();

            if (insert != 0)
            {
                Upd = true;
            }
            return Upd;

        }

        public List<KH_NGHIEP_VU> get_all_KH_NGHIEP_VU()
        {
            List<KH_NGHIEP_VU> member = new List<KH_NGHIEP_VU>();
            DAL.ConnectDB.connect();
            var cmd = DAL.ConnectDB.connection.CreateCommand();
            cmd.CommandText = "get_all_kh_nghiep_vu.get_all_kh_nghiep_vu_by";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("cursor_nghiep_vu", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                KH_NGHIEP_VU nv = new KH_NGHIEP_VU();
                nv.Id = Convert.ToInt64(reader["ID"]);
                nv.Ma_nv = Convert.ToString(reader["MA_NV"]);
                nv.Ten_nv = Convert.ToString(reader["TEN_NV"]);
                nv.Ngay_bd = Convert.ToDateTime(reader["NGAYBD"]);
                member.Add(nv);
            }
            return member.ToList();
        }
        public List<KH_NGHIEP_VU> search_kh_nghiep_vu(string key)
        {
            List<KH_NGHIEP_VU> member = new List<KH_NGHIEP_VU>();
            DAL.ConnectDB.connect();
            var cmd = DAL.ConnectDB.connection.CreateCommand();
            cmd.CommandText = "get_all_kh_nghiep_vu.search_kh_nghiep_vu_by";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("s_key", OracleDbType.Varchar2).Value = key;
            cmd.Parameters.Add("cs_search_nghiep_vu", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                KH_NGHIEP_VU nv = new KH_NGHIEP_VU();
                nv.Id = Convert.ToInt64(reader["ID"]);
                nv.Ma_nv = Convert.ToString(reader["MA_NV"]);
                nv.Ten_nv = Convert.ToString(reader["TEN_NV"]);
                nv.Ngay_bd = Convert.ToDateTime(reader["NGAYBD"]);
                member.Add(nv);
            }
            return member.ToList();
        }
        public bool del_KH_NGHIEP_VU(Int64 id_nv)
        {
            bool Del = false;
            DAL.ConnectDB.connect();
            var cmd = DAL.ConnectDB.connection.CreateCommand();
            cmd.CommandText = "edit_kh_nghiep_vu.del_kh_nghiep_vu_by";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("id_nghiep_vu", OracleDbType.Int64).Value = id_nv;
            int insert = cmd.ExecuteNonQuery();

            if (insert != 0)
            {
                Del = true;
            }
            return Del;

        }

    }
}
